package pers.lt.jdbc;

import pers.lt.tools.StringFacs;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;

import java.io.IOException;
import java.util.Properties;

/**
 * Created by liting on 2015/11/20.
 */
public class PageSql {
    private boolean bigData = false;
    private String dbDriver;

    public PageSql() {
        Resource resource = null;
        Properties props = null;
        String driverClass = null;
        try {
            resource = new ClassPathResource("/db.properties");
            props = PropertiesLoaderUtils.loadProperties(resource);
            driverClass = (String) props.get("jdbc.driver");
        } catch (IOException e) {
            e.printStackTrace();
        }
        dbDriver = driverClass;

    }

    public String getSql(String sql, Page page) {
        String ls_Sql = sql;
        while (ls_Sql.indexOf("  ") > -1)  ls_Sql = ls_Sql.replaceAll("  ", " ");
        int pageSize = Integer.parseInt(page.getRows());
        int pageNum = Integer.parseInt(page.getPage());
        String primaryKey = page.getPrimarykey();

        //分页
        if (!this.bigData) {//普通表的分页
            if (dbDriver.indexOf("oracle") > -1) {
                ls_Sql = " select * from "
                        + " (select a.*,rownum as row_num from  " + " (" + ls_Sql
                        + " ) a " + " )  b where b.row_num  between "
                        + (pageSize * (pageNum - 1) + 1) + " and "
                        + (pageSize * (pageNum - 1) + pageSize);
            } else if (dbDriver.indexOf("sqlserver") > -1 || dbDriver.indexOf("sql server") > -1) {
                //select top M-N * from yourTable where id not in(select top N-1 id from table)
                if (ls_Sql.indexOf("order by") > -1) {
                    int selectPos = ls_Sql.indexOf("select ");
                    ls_Sql = ls_Sql.substring(0, selectPos + 7) + " top "
                            + pageSize * pageNum + " "
                            + ls_Sql.substring(selectPos + 7, ls_Sql.length());
                }

                ls_Sql = " select top " + pageSize + " * " + " from "
                        + " (" + ls_Sql + " ) as a " + " where a."
                        + primaryKey + " not in ( select top  "
                        + pageSize * (pageNum - 1) + " "
                        + primaryKey + " from (" + ls_Sql + ") b)";
            } else if (dbDriver.indexOf("db2") > -1) {
                String ls_BaseTable = ls_Sql;
                if (pageNum <= 1)
                    ls_Sql = "select yysdb2temp.* from (" + ls_BaseTable + ") yysdb2temp fetch first " + pageSize + " rows only";
                else {
                    ls_Sql = "select yysdb2temp.* from (" + ls_BaseTable + ") yysdb2temp where " + primaryKey + " " +
                            " not in ( select yysdb2temp1." + primaryKey + "  from (" + ls_BaseTable + ") yysdb2temp1 " +
                            "          fetch first " + pageSize * (pageNum - 1) + " rows only" +
                            ") " +
                            "fetch first " + pageSize + " rows only";
                }

            } else { //mysql
                ls_Sql = ls_Sql
                        + "  limit   "
                        + pageSize * (pageNum - 1) + ","
                        + pageSize;

            }
        } else {//大表的分页
            if (dbDriver.indexOf("oracle") > -1) {
                String ls_BaseTable = ls_Sql;
                int li_resultNum = (pageSize * pageNum) + 1;
                ls_Sql = "select * from (" + ls_BaseTable + " ) where rownum<" + li_resultNum;
            } else if (dbDriver.indexOf("sqlserver") > -1 || dbDriver.indexOf("sql server") > -1) {
                String ls_BaseTable = ls_Sql;
                int li_resultNum = (pageSize * pageNum) + 1;
                ls_Sql = "select top " + li_resultNum + " * from (" + ls_BaseTable + " ) baseTable";
            } else if (dbDriver.indexOf("db2") > -1) {
                String ls_BaseTable = ls_Sql;
                int li_resultNum = (pageSize * pageNum) + 1;
                ls_Sql = "select yysdb2temp.* from (" + ls_BaseTable + ") yysdb2temp fetch first " + li_resultNum + " rows only";
            }
        }
        if(!StringFacs.isEmpty(page.getOrderby())) ls_Sql+= page.getOrderby();
            return ls_Sql;
    }
}
